import pymysql

#定义数据库   工具类
class DBTool(object):
    #建立连接 -静态方法 -私有(__)
    @staticmethod
    def __create_conn():
        # 指定 IP，port，用户名，密码，数据库，返回数据
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2022", database="ihrm", charset="utf8")
        return conn

    #查询方法
    @staticmethod
    def query_one(sql):
        conn = None
        cursor = None
        res = None
        try:
            # 获取连接，创建游标，执行sql，提取结果集，返回一条结果
            conn = DBTool.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print("查询sql失败：",err)
        finally:
            cursor.close()
            conn.close()
            return res
    @staticmethod
    def query_many(sql,n):
        conn = None
        cursor = None
        res = None
        try:
            # 获取连接，创建游标，执行sql，提取结果集，返回一条结果
            conn = DBTool.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(n)
        except Exception as err:
            print("查询sql失败：",err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_all(sql):
        conn = None
        cursor = None
        res = None
        try:
            # 获取连接，创建游标，执行sql，提取结果集，返回一条结果
            conn = DBTool.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchall()
        except Exception as err:
            print("查询sql失败：", err)
        finally:
            cursor.close()
            conn.close()
            return res

    #增删改 方法
    @staticmethod
    def db_idu(sql):
        conn = None
        cursor = None
        try:
            #  创建连接
            conn = DBTool.__create_conn()
            #  创建游标
            cursor = conn.cursor()
            #  执行 sql语句。
            ret = cursor.execute(sql)
            print("Affected rows:", ret)
            #  执行成功，提交事务。 commit()
            conn.commit()
        except Exception as err:
            # 执行失败，回滚事务
            conn.rollback()
            print("失败", err)
        finally:
            #  关闭游标
            cursor.close()
            #  关闭连接
            conn.close()

if __name__ == '__main__':
    res = DBTool.query_one("select * from t_book;")
    print("查一条",res)

    res = DBTool.query_many("select * from t_book;",2)
    print("查多条", res)

    res = DBTool.query_all("select * from t_book;")
    print("查全部", res)

    DBTool.db_idu("insert into t_book(id, title, pub_date) VALUES(157, '三国志', '1986-01-01');")

    DBTool.db_idu("update t_book set `read`= `read`+1 where title = '三国志';")

    DBTool.db_idu("DELETE  FROM t_book where title = '三国志';")